from sqlalchemy import Column, Integer, String, Text, Boolean, DateTime, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from ..base import Base


class SpecificationGroup(Base):
    """
    商品规格分组表 - 用于管理规格分组（如显示参数、硬件配置等）
    """
    __tablename__ = "product_specification_groups"

    id = Column(Integer, primary_key=True, index=True, comment="规格分组ID")
    name = Column(String(100), nullable=False, unique=True, comment="分组名称")
    description = Column(Text, nullable=True, comment="分组描述")
    sort_order = Column(Integer, default=0, comment="排序顺序")
    is_active = Column(Boolean, default=True, comment="是否激活")

    created_at = Column(DateTime(timezone=True), server_default=func.now(), comment="创建时间")
    updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), comment="更新时间")

    # 关联规格定义
    specifications = relationship("SpecificationDefinition", backref="group")

    def __repr__(self):
        return f"<SpecificationGroup(id={self.id}, name='{self.name}')>"


class SpecificationDefinition(Base):
    """
    商品规格定义表 - 用于定义规格类型（如屏幕尺寸、内存容量等）
    符合第三范式：规格定义与规格值分离
    """
    __tablename__ = "product_specification_definitions"

    id = Column(Integer, primary_key=True, index=True, comment="规格定义ID")
    group_id = Column(Integer, ForeignKey("product_specification_groups.id", ondelete="SET NULL"), nullable=True, comment="规格分组ID")
    name = Column(String(100), nullable=False, unique=True, comment="规格名称")
    code = Column(String(50), nullable=False, unique=True, comment="规格代码")
    data_type = Column(String(20), default="text", comment="数据类型：text, number, boolean等")
    unit = Column(String(20), nullable=True, comment="单位（如英寸、GB等）")
    description = Column(Text, nullable=True, comment="规格描述")
    sort_order = Column(Integer, default=0, comment="排序顺序")
    is_active = Column(Boolean, default=True, comment="是否激活")

    created_at = Column(DateTime(timezone=True), server_default=func.now(), comment="创建时间")
    updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), comment="更新时间")

    # 关联规格值
    values = relationship("ProductSpecification", backref="definition")

    def __repr__(self):
        return f"<SpecificationDefinition(id={self.id}, name='{self.name}', code='{self.code}')>"


class ProductSpecification(Base):
    """
    商品规格值表 - 用于存储商品的具体规格值
    符合第三范式：规格值与规格定义分离
    """
    __tablename__ = "product_specifications"

    id = Column(Integer, primary_key=True, index=True, comment="规格ID")
    product_id = Column(Integer, ForeignKey("products.id", ondelete="CASCADE"), nullable=False, comment="商品ID")
    specification_id = Column(Integer, ForeignKey("product_specification_definitions.id", ondelete="CASCADE"), nullable=False, comment="规格定义ID")
    value = Column(String(500), nullable=False, comment="规格值")
    sort_order = Column(Integer, default=0, comment="排序顺序")
    is_active = Column(Boolean, default=True, comment="是否激活")

    created_at = Column(DateTime(timezone=True), server_default=func.now(), comment="创建时间")
    updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), comment="更新时间")

    def __repr__(self):
        return f"<ProductSpecification(id={self.id}, product_id={self.product_id}, specification_id={self.specification_id})>"